﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace SnongPhoneShop.Models
{
    public class StoredProcedure
    {
        SnongPhoneShopEntities db = new SnongPhoneShopEntities();

        /************************************************************************/
        /*                     Get Function Field                -              */
        /************************************************************************/
        #region [Get]

        public List<Banner> GetListBanner()
        {
            return db.Banners.ToList();
        }
        public List<SanPham> GetListProduct()
        {
            return db.SanPhams.ToList();
        }
        public SanPham GetProductByID(string id)
        {
            return db.SanPhams.FirstOrDefault(m => m.MaSanPham.Equals(id) && m.Publish == true && m.DanhMucSanPham.Publish == true);
        }
        public SanPham GetProductByID(string id, bool publishedRequire)
        {
            if (publishedRequire) return GetProductByID(id);
            else return db.SanPhams.FirstOrDefault(m => m.MaSanPham.Equals(id));
        }

        public List<SanPham> GetListProductByCatalogID(int id)
        {
            return db.SanPhams.Where(m => m.MaDanhMuc == id && m.Publish == true && m.DanhMucSanPham.Publish == true).ToList();
        }

        public List<DanhMucSanPham> GetListCatalogByTypeID(int id)
        {
            if (id == 0) return db.DanhMucSanPhams.Where(m => m.Publish == true).ToList();
            return db.DanhMucSanPhams.Where(m => m.Publish == true && m.MaLoaiDanhMuc == id).ToList();
        }

        public List<SanPham> GetListProductByTypeID(int id)
        {
            return db.SanPhams.Where(m => m.DanhMucSanPham.MaLoaiDanhMuc == id && m.Publish == true && m.DanhMucSanPham.Publish == true).ToList();
        }

        public DanhMucSanPham GetCatalogByID(int id)
        {
            return db.DanhMucSanPhams.FirstOrDefault(m => m.MaDanhMuc == id && m.Publish == true);
        }

        public List<YKienKhachHang> GetListCommentOfProductByID(string id)
        {
            return db.YKienKhachHangs.Where(m => m.MaSanPham.Equals(id) && m.SanPham.Publish == true && m.SanPham.DanhMucSanPham.Publish == true).OrderByDescending(m => m.MaYKien).ToList();
        }

        public List<Tinh_TP> GetListProvince()
        {
            return db.Tinh_TP.ToList();
        }

        public KhachHang GetUserByID(string id)
        {
            return db.KhachHangs.FirstOrDefault(m => m.TenDangNhap.Equals(id));
        }

        public List<PhieuDatHang> GetOrdersByUserID(string id)
        {
            var ListDH = db.PhieuDatHangs.Where(m => m.TenDangNhapKH.Equals(id)).ToList();
            return ListDH;
        }

        public KhachHang GetUserByID(string id, string pw)
        {
            return db.KhachHangs.FirstOrDefault(m => m.TenDangNhap.Equals(id) && m.MatKhau.Equals(pw));
        }

        public List<SanPham> GetListProductByName(string name)
        {
            return db.SanPhams.Where(m => m.TenSanPham.Contains(name) && m.Publish == true && m.DanhMucSanPham.Publish == true).OrderBy(m => m.TenSanPham).ToList();
        }

        public List<LoaiDanhMuc> GetListTypeCatalog()
        {
            return db.LoaiDanhMucs.ToList();
        }

        public List<SanPham> GetListProductByName(string name, int startPrice, int endPrice, int catalogType, int catalogID)
        {
            var L = GetListProductByName(name);
            if (startPrice != 0 && endPrice != 0)
            {
                L = L.Where(m => m.DonGia >= startPrice && m.DonGia <= endPrice).ToList();
            }
            else
            {
                if (startPrice == 0 && endPrice == 0) { }
                else
                {
                    if (startPrice == 0)
                    {
                        L = L.Where(m => m.DonGia <= endPrice).ToList();
                    }
                    else
                    {
                        L = L.Where(m => m.DonGia >= startPrice).ToList();
                    }
                }
            }
            if (catalogID == 0)
            {
                if (catalogType == 0)
                {
                    return L.ToList();
                }
                else
                {
                    return L.Where(m => m.DanhMucSanPham.MaLoaiDanhMuc == catalogType).ToList();
                }
            }
            else
            {
                return L.Where(m => m.MaDanhMuc == catalogID).ToList();
            }
        }

        #endregion
        /************************************************************************/
        /*                     Set Function Field                               */
        /************************************************************************/
        #region [Set]

        public void SetPublishStatusOfListProduct(string list_id, bool status)
        {
            var arr_id = list_id.Split(',');
            foreach (var Item in arr_id)
            {
                var p = GetProductByID(Item, false);
                p.Publish = status;
            }
            db.SaveChanges();
        }

        public void SetFeatureStatusOfListProduct(string list_id, bool status)
        {
            var arr_id = list_id.Split(',');
            foreach (var Item in arr_id)
            {
                var p = GetProductByID(Item, false);
                p.SPNoiBat = status;
            }
            db.SaveChanges();
        }

        public void SetPasswordForUserByID(string id, string password)
        {
            var U = GetUserByID(id);
            U.MatKhau = password;
            db.SaveChanges();
        }

        #endregion
        /************************************************************************/
        /*                     Other Function Field                             */
        /************************************************************************/
        #region [Add - Delete - Other]

        public void DeleteProductByID(string id)
        {
            try
            {
                var p = GetProductByID(id, false);
                RemoveLinkedProduct(ref p);
                db.SanPhams.Remove(p);
                db.SaveChanges();
            }
            catch { }
        }
        public void DeleteListProduct(string list_id)
        {
            var arr_id = list_id.Split(',');
            foreach (var Item in arr_id)
            {
                DeleteProductByID(Item);
            }
        }

        private void RemoveLinkedProduct(ref SanPham p)
        {
            p.SanPhamDT.Clear();
            p.SanPhamPK.Clear();
        }

        public DanhMucSanPham getDMSPbyId(int id)
        {
            return db.DanhMucSanPhams.First(m=>m.MaDanhMuc==id);
        }

        public List<SanPham> PagingListProduct(List<SanPham> L, int page, int pageSize, out int pageCount, out int startPage, out int endPage)
        {
            pageCount = L.Count % pageSize == 0 ? L.Count / pageSize : L.Count / pageSize + 1;
            startPage = page <= 5 ? 1 : page - 4;
            startPage = pageCount - 8 < startPage ? pageCount - 8 : startPage;
            startPage = startPage < 1 ? 1 : startPage;
            endPage = pageCount >= startPage + 8 ? startPage + 8 : pageCount;
            return L.Skip((page - 1) * pageSize).Take(pageSize).ToList();
        }

        public int AddUserByRegisterModel(RegisterModels R)
        {
            var U = GetUserByID(R.UserID);
            if (U != null) return 0;
            else
            {
                if (isExistUserEmail(R.Email)) return 1;
                if (isExistUserCMT(R.CMT)) return 2;
                KhachHang KH = new KhachHang();
                KH.TenDangNhap = R.UserID;
                KH.HoTen = R.HoTen;
                KH.Email = R.Email;
                KH.MatKhau = R.Password;
                KH.NgaySinh = R.NgaySinh;
                KH.SoDT = R.SoDT;
                KH.DiaChi = R.DiaChi;
                KH.MaTinh_TP = R.MaTinh_TP;
                KH.CMT = R.CMT;
                db.KhachHangs.Add(KH);
                db.SaveChanges();
                return 3;
            }
        }

        public bool isValidToChangePass(string id, string email, string cmt)
        {
            var U = db.KhachHangs.FirstOrDefault(m => m.TenDangNhap.Equals(id) && m.Email.Equals(email) && m.CMT.Equals(cmt));
            if (U != null) return true;
            else return false;
        }

        private bool isExistUserEmail(string email)
        {
            var U = db.KhachHangs.FirstOrDefault(m => m.Email.Equals(email));
            if (U != null) return true;
            else return false;
        }

        private bool isExistUserCMT(string cmt)
        {
            var U = db.KhachHangs.FirstOrDefault(m => m.CMT.Equals(cmt));
            if (U != null) return true;
            else return false;
        }

        public string GenIDProduct(string keyword)
        {
            string key = keyword.ToUpper();
            if (keyword.Length > 4) key = keyword.Substring(0, 4).ToUpper();
            Random rd = new Random();
            int length = Convert.ToInt16(rd.NextDouble() * (10 - key.Length - 1) + 1);
            for (int i = 0; i < length; i++)
            {
                int type = Convert.ToInt32(rd.NextDouble() * 2);
                if (type == 1)
                {
                    char c = Convert.ToChar(Convert.ToInt32(rd.NextDouble() * 25 + 65));
                    key += c.ToString();
                }
                else
                {
                    int n = Convert.ToInt32(rd.NextDouble() * 9);
                    key += n.ToString();
                }
            }
            if (!isExistProductKey(key)) return key;
            else return GenIDProduct(keyword);
        }

        public bool isExistProductKey(string key)
        {
            if (db.SanPhams.FirstOrDefault(m => m.MaSanPham.Equals(key)) != null) return true;
            else return false;
        }

        public int CreateBillForUserByID(string id)
        {
            PhieuDatHang PDH = new PhieuDatHang();
            PDH.TenDangNhapKH = id;
            PDH.Unread = true;
            PDH.NgayLapPhieu = DateTime.Now;
            PDH.MaTrangThai = 1;
            db.PhieuDatHangs.Add(PDH);
            db.SaveChanges();
            return db.PhieuDatHangs.Max(m => m.MaPhieuDatHang);
        }

        public int CreateBillByInfo(string name, string email, string id, string phone, string address)
        {
            PhieuDatHang PDH = new PhieuDatHang();
            PDH.HoTen = name;
            PDH.NgayLapPhieu = DateTime.Now;
            PDH.MaTrangThai = 1;
            PDH.Unread = true;
            PDH.Email = email;
            PDH.CMT = id;
            PDH.SoDT = phone;
            PDH.DiaChi = address;
            db.PhieuDatHangs.Add(PDH);
            db.SaveChanges();
            return db.PhieuDatHangs.Max(m => m.MaPhieuDatHang);
        }
        public void AddItemCartToBill(int bill_id, Cart p)
        {
            CT_PhieuDatHang CT_PDH = new CT_PhieuDatHang();
            CT_PDH.MaPhieuDatHang = bill_id;
            CT_PDH.MaSanPham = p.MaSanPham;
            CT_PDH.SoLuong = p.SoLuong;
            db.CT_PhieuDatHang.Add(CT_PDH);
            db.SaveChanges();
        }

        public void UpdateProductByID(string id, SanPham p)
        {
            var currentP = db.SanPhams.FirstOrDefault(m => m.MaSanPham.Equals(id));
            currentP.AmBao = p.AmBao;
            currentP.AnhDaiDien = p.AnhDaiDien;
            currentP.BangTan = p.BangTan;
            currentP.BaoHanh = p.BaoHanh;
            currentP.Bluetooth = p.Bluetooth;
            currentP.BoNhoTrong = p.BoNhoTrong;
            currentP.C3G = p.C3G;
            currentP.CamUng = p.CamUng;
            currentP.ChieuCao = p.ChieuCao;
            currentP.ChieuDai = p.ChieuDai;
            currentP.ChieuRong = p.ChieuRong;
            currentP.Chip = p.Chip;
            currentP.DanhBaLuuTru = p.DanhBaLuuTru;
            currentP.DinhDangNhac = p.DinhDangNhac;
            currentP.DinhDangPhim = p.DinhDangPhim;
            currentP.DonGia = p.DonGia;
            currentP.DoPhanGiai = p.DoPhanGiai;
            currentP.EDGE = p.EDGE;
            currentP.GhiChu = p.GhiChu;
            currentP.GPRS = p.GPRS;
            currentP.GPS = p.GPS;
            currentP.HeDieuHanh = p.HeDieuHanh;
            currentP.HongNgoai = p.HongNgoai;
            currentP.HoTroJava = p.HoTroJava;
            currentP.HoTroNhieuSim = p.HoTroNhieuSim;
            currentP.HoTroTheNho = p.HoTroTheNho;
            currentP.JackAudio = p.JackAudio;
            currentP.KichThuocManHinh = p.KichThuocManHinh;
            currentP.LoaNgoai = p.LoaNgoai;
            currentP.MaCamUng = p.MaCamUng;
            currentP.MaDanhMuc = p.MaDanhMuc;
            currentP.MaKieuDang = p.MaKieuDang;
            currentP.MaKieuManHinh = p.MaKieuManHinh;
            currentP.MaLoaiBanPhim = p.MaLoaiBanPhim;
            //currentP.MaSanPham = p.MaSanPham;
            currentP.MaTinhTrang = p.MaTinhTrang;
            currentP.MaTrangThaiPhatHanh = p.MaTrangThaiPhatHanh;
            currentP.MauSac = p.MauSac;
            currentP.MayAnhChinh = p.MayAnhChinh;
            currentP.MayAnhPhu = p.MayAnhPhu;
            currentP.MoTa = p.MoTa;
            currentP.NgayPhatHanh = p.NgayPhatHanh;
            currentP.NgonNgu = p.NgonNgu;
            currentP.PhuKienKemTheo = p.PhuKienKemTheo;
            currentP.Pin = p.Pin;
            currentP.Publish = p.Publish;
            currentP.QuaTang = p.QuaTang;
            currentP.Radio = p.Radio;
            currentP.RAM = p.RAM;
            currentP.SPNoiBat = p.SPNoiBat;
            currentP.TenSanPham = p.TenSanPham;
            currentP.TinhNangKhac = p.TinhNangKhac;
            currentP.TinhNangNoiBat = p.TinhNangNoiBat;
            currentP.TrinhDuyetWeb = p.TrinhDuyetWeb;
            currentP.TrongLuong = p.TrongLuong;
            currentP.UngDung = p.UngDung;
            currentP.USB = p.USB;
            currentP.Wifi = p.Wifi;
            db.SaveChanges();
        }

        public bool isValidPassword(string id, string password)
        {
            var U = GetUserByID(id);
            return U.MatKhau.Equals(password);
        }

        public int UpdateAccountInfoByID(string id, RegisterModels R)
        {
            var TK = GetUserByID(id);
            if (isExistUserEmail(R.Email) && !R.Email.Equals(TK.Email)) return 1;
            if (isExistUserCMT(R.CMT) && !R.CMT.Equals(TK.CMT)) return 2;
            TK.HoTen = R.HoTen;
            TK.NgaySinh = R.NgaySinh;
            TK.SoDT = R.SoDT;
            TK.Email = R.Email;
            TK.DiaChi = R.DiaChi;
            TK.CMT = R.CMT;
            TK.MaTinh_TP = R.MaTinh_TP;
            db.SaveChanges();
            return 3;
        }

        public RegisterModels GetRegisterModelsByID(string id)
        {
            var TK = GetUserByID(id);
            RegisterModels R = new RegisterModels
            {
                UserID = TK.TenDangNhap,
                HoTen = TK.HoTen,
                Email = TK.Email,
                NgaySinh = TK.NgaySinh.Date,
                SoDT = TK.SoDT,
                DiaChi = TK.DiaChi,
                CMT = TK.CMT,
                MaTinh_TP = TK.MaTinh_TP,
                Password = "******",
                ConfirmPassword = "******"
            };
            return R;
        }

        public void AddProduct(SanPham p)
        {
            db.SanPhams.Add(p);
            db.SaveChanges();
        }

        public void ReversePublishStatusOfProductByID(string id)
        {
            var p = GetProductByID(id, false);
            p.Publish = !p.Publish;
            db.SaveChanges();
        }
        public void ReversePublishStatusOfBannerByID(int id)
        {
            var p = GetListBanner().FirstOrDefault(m => m.MaAnh == id);
            p.Publish = !p.Publish;
            db.SaveChanges();
        }
        public void ReverseFeatureStatusOfProductByID(string id)
        {
            var p = GetProductByID(id, false);
            p.SPNoiBat = !p.SPNoiBat;
            db.SaveChanges();
        }
        public void DeleteBannerByID(int id)
        {
            try
            {
                var b = db.Banners.FirstOrDefault(m => m.MaAnh == id);
                db.Banners.Remove(b);
                db.SaveChanges();
            }
            catch { }
        }
        public void AddComment(string txt, string id_sp, string user)
        {
            YKienKhachHang YK = new YKienKhachHang();
            YK.MaSanPham = id_sp;
            YK.NoiDung = txt;
            YK.TenDangNhapKH = user;
            YK.ThoiGian = DateTime.Now;
            db.YKienKhachHangs.Add(YK);
            db.SaveChanges();
        }

        public void DeleteComment(int id)
        {
            var cmt = db.YKienKhachHangs.First(m => m.MaYKien == id);
            db.YKienKhachHangs.Remove(cmt);
            db.SaveChanges();
        }

        #endregion
    }
}